package com.geotmt.billingcenter.modules.consume.mapper.provider;

import com.alibaba.druid.util.StringUtils;
import com.geotmt.billingcenter.common.entry.ConsumeBaseEntry;
import com.geotmt.billingcenter.common.vo.consume.ConsumeVo;
import com.geotmt.billingcenter.common.vo.params.ParamsConsume;
import com.geotmt.billingcenter.common.vo.params.ParamsForDel;
import org.apache.ibatis.annotations.Param;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @Description 消耗 Base Sql类
 * @Author      yanghanwei
 * @Mail        yanghanwei@geotmt.com
 * @Date        2019/3/13 11:01
 * @Version     v1
 **/
public class ConsumeBaseProvider extends WhereProvider {

    private static final Logger logger = LoggerFactory.getLogger(ConsumeBaseProvider.class);

    private static final int NORMAL_STATUS= 1;
    private static final int DELETE_STATUS= 2;

    /**
     * 消耗 Base add
     * @return
     */
    public String add(ConsumeBaseEntry consumeBase){
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO fin_consume_curr_conf_base (product_code,company_id,cuser_id,interface_id,fee_flag,effect_time,")
                .append(" create_user_id,create_time,`status`,`desc`,label,r_id)")
                .append(" VALUES")
                .append(" (#{productCode},#{companyId},#{cuserId},#{interfaceId},#{feeFlag},#{effectTime},")
                .append(" #{createUserId},#{createTime}," + NORMAL_STATUS + ",#{desc},#{label},#{rId})");
        logger.info("消耗 subBase add SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗 Base update
     * feeFlag  updateUserId updateTime
     * @return
     */
    public String update(ConsumeVo consumeRule){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_consume_curr_conf_base SET r_id=#{rId},update_user_id=#{updateUserId},effect_time=#{effectTime},create_user_id=#{createUserId},create_time=#{createTime},update_time=#{updateTime},fee_flag=#{feeFlag} where id=#{id}");
        logger.info("消耗 subBase update SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗 Base delete
     * @return
     */
    public String delete(ParamsForDel id){
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE fin_consume_curr_conf_base SET update_user_id=#{updateUserId},update_time=#{updateTime},status=" + DELETE_STATUS + " where id=#{id}");
        logger.info("消耗 subBase delete SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗 list
     * @param obj
     * @return
     */
    public String list(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT curr.id, curr.product_code productCode,curr.company_id companyId,curr.cuser_id cuserId,curr.interface_id interfaceId,curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime,curr.create_user_id createUserId,curr.create_time createTime,curr.update_user_id updateUserId,")
                .append(" curr.update_time updateTime,curr.`status`,curr.`desc`,")
                .append(" consume.price,consume.discount,consume.`id` rId,consume.`stair_cycle` stairCycle,consume.`stair_start_time` stairStartTime,")
                .append(" com.company_full_name companyName, ")
                .append(" cuser.account cuserNo,cuser.`real_name` cuserName,")
                .append(" inter.interface_name interfaceName,")
                .append(" cuserCre.real_name,")
                .append(" cuserUp.real_name")
                .append(" FROM `fin_consume_curr_conf_base` curr  ")
                .append(" LEFT JOIN `fin_consume_curr_conf_rule` consume ON curr.r_id=consume.id")
                .append(" LEFT JOIN `t_company_info` com ON curr.`company_id`=com.`id`")
                .append(" LEFT JOIN `t_cuser_info` cuser ON curr.`cuser_id`=cuser.`id`")
                .append(" LEFT JOIN `t_interface` inter ON curr.`interface_id`=inter.`id`")
                .append(" LEFT JOIN `sys_user` cuserCre ON curr.`create_user_id`= cuserCre.`id`")
                .append(" LEFT JOIN `sys_user` cuserUp ON curr.`update_user_id`=cuserUp.`id`")
                .append(" WHERE 1=1");
        whereConsume(sql,"curr", obj);
        logger.info("消耗 list SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 查询listAll （添加或修改的回显）
     * @param obj
     * @return
     */
    public String listAll(ParamsConsume obj){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT inter.interface_name interfaceName, inter.id interfaceId, curr.id, #{productCode} productCode, #{companyId} companyId, #{cuserId} cuserId, curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime, curr.`desc`, consume.price, consume.discount, consume.`id` rId, consume.`stair_cycle` stairCycle, consume.`stair_start_time` stairStartTime, com.company_full_name companyName,")
                .append(" curr.create_user_id createUserId, curr.create_time createTime")
                .append(" FROM")
                .append(" (SELECT ter.id, ter.interface_name")
                .append(" FROM t_interface ter")
                .append(" WHERE ter.id IN")
                .append(" (SELECT DISTINCT ti.id")
                .append(" FROM t_cuser_interface tci")
                .append(" LEFT JOIN t_interface ti ON tci.`interface_id` = ti.`id`")
                .append(" LEFT JOIN t_cuser_info `user` ON tci.`cuser_id` = user.id")
                .append(" LEFT JOIN t_product tp ON tp.`id` = ti.`product_id`")
                .append(" WHERE 1 = 1");
        whereConsumeListForInter(sql,obj);
        sql.append(")) inter")
                .append(" LEFT JOIN ( ")
                .append(" SELECT cur.interface_id, cur.id, cur.product_code, cur.company_id, cur.cuser_id, cur.fee_flag, cur.effect_time, cur.`desc`,")
                .append(" cur.create_user_id, cur.create_time, cur.r_id")
                .append(" FROM `fin_consume_curr_conf_base` cur")
                .append(" WHERE 1=1");
        whereConsumeListAllForCurr(sql,"cur",obj);
                sql.append(") curr")
                .append(" ON curr.`interface_id` = inter.`id`")
                .append(" LEFT JOIN `fin_consume_curr_conf_rule` consume")
                .append(" ON curr.r_id = consume.id")
                .append(" LEFT JOIN `t_company_info` com")
                .append(" ON curr.`company_id` = com.`id`")
                .append(" WHERE 1=1");
        whereConsumeListAllForAll(sql,"inter", obj);
        logger.info("消耗 listAll SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 消耗 getById
     * @param id
     * @return
     */
    public String getById(Integer id){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT curr.id, curr.product_code productCode,curr.company_id companyId,curr.cuser_id cuserId,curr.interface_id interfaceId,curr.fee_flag feeFlag,")
                .append(" curr.effect_time effectTime,curr.create_user_id createUserId,curr.create_time createTime,curr.update_user_id updateUserId,")
                .append(" curr.update_time updateTime,curr.`status`,curr.`desc`,")
                .append(" consume.price,consume.discount,consume.`id` rId,consume.`stair_cycle` stairCycle,consume.`stair_start_time` stairStartTime,")
                .append(" com.company_full_name companyName, ")
                .append(" cuser.account userNo,cuser.`real_name` userName,")
                .append(" inter.interface_name interfaceName,")
                .append(" cuserCre.real_name,")
                .append(" cuserUp.real_name")
                .append(" FROM `fin_consume_curr_conf_base` curr  ")
                .append(" LEFT JOIN `fin_consume_curr_conf_rule` consume ON curr.r_id=consume.id")
                .append(" LEFT JOIN `t_company_info` com ON curr.`company_id`=com.`id`")
                .append(" LEFT JOIN `t_cuser_info` cuser ON curr.`cuser_id`=cuser.`id`")
                .append(" LEFT JOIN `t_interface` inter ON curr.`interface_id`=inter.`id`")
                .append(" LEFT JOIN `sys_user` cuserCre ON curr.`create_user_id`= cuserCre.`id`")
                .append(" LEFT JOIN `sys_user` cuserUp ON curr.`update_user_id`=cuserUp.`id`")
                .append(" WHERE 1=1 AND curr.id=#{id}");
        logger.info("消耗 getById SQL:{}",sql);
        return sql.toString();
    }

    /**
     * 获取当前有效的规则
     * @param code
     * @param companyId
     * @param cuserId
     * @param interfaceId
     * @param date
     * @return
     */
    public String getConsume(@Param(value = "code") String code,
                             @Param(value = "companyId") Integer companyId,
                             @Param(value = "cuserId") Integer cuserId,
                             @Param(value = "interfaceId") Integer interfaceId,
                             @Param(value = "date") String date){
        StringBuffer sql = new StringBuffer();

        sql.append("SELECT t1.id,t1.r_id rId, t1.product_code productCode,t1.cuser_id cuserId,t1.interface_id interfaceId,t1.fee_flag feeFlag, t1.effect_start_time effectStartTime ,t1.effect_end_time effectEndTime,t1.status,t1.label,")
                .append("t1.price,t1.discount,t1.stair_cycle stairCycle,t1.stair_start_time stairStartTime,t1.`sign`,'consume' `type`")
                .append(" FROM (")
                .append(" SELECT * FROM (")
                .append(" SELECT base.id,base.r_id,base.product_code,base.company_id, base.cuser_id,base.interface_id,base.fee_flag,DATE_FORMAT(base.effect_time,'%Y-%m-%d') effect_start_time, CURDATE() effect_end_time,base.`status`,base.label,")
                .append(" consume.`price`,consume.`discount`,consume.`stair_cycle`,consume.`stair_start_time`,'curr' `sign`")
                .append(" FROM fin_consume_curr_conf_base base")
                .append(" LEFT JOIN `fin_consume_curr_conf_rule` consume ON consume.id = base.`r_id`) t")
                .append(" WHERE t.status =1 AND t.cuser_id=#{cuserId} AND t.interface_id=#{interfaceId} AND t.effect_start_time <= CURDATE()");
        whereForgetMoney(sql, "t", code, companyId );
        sql.append(" UNION ALL ")
                .append(" SELECT baseHis.id,baseHis.r_id,baseHis.product_code,baseHis.company_id, baseHis.cuser_id,baseHis.interface_id,baseHis.fee_flag,DATE_FORMAT(baseHis.effect_start_time,'%Y-%m-%d') effect_start_time, DATE_FORMAT(baseHis.effect_end_time,'%Y-%m-%d') effect_end_time,baseHis.`status`,baseHis.label,")
                .append(" ruleHis.`price`,ruleHis.`discount`,ruleHis.`stair_cycle`,ruleHis.`stair_start_time`,'his' `sign`")
                .append(" FROM fin_consume_his_conf_base baseHis")
                .append(" LEFT JOIN `fin_consume_his_conf_rule` ruleHis ON ruleHis.id = baseHis.`r_id`")
                .append(" WHERE baseHis.status =1  AND baseHis.cuser_id=#{cuserId}  AND baseHis.interface_id=#{interfaceId}");
        whereForgetMoney(sql, "baseHis", code, companyId );
        sql.append(" ) t1")
                .append(" WHERE #{date}>=t1.effect_start_time AND #{date}<=t1.effect_end_time")
                .append(" ORDER BY t1.effect_start_time");
        logger.info("getConsume SQL:{}",sql);
        return sql.toString();
    }
}
